package com.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Khaled Lela
 */
public class DistributorSql {

    private Connection con;
    private PreparedStatement ps;
    private ResultSet rs;
    private boolean error;
    private int quantity;

    public DistributorSql() {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby:BreadDB;");
        } catch (Exception ex) {
        }
    }

    //insert Distributor data Row
    public boolean setDistributorRow(String name, String region, int phone) {
        try {
            ps = con.prepareStatement("insert into Distributor(Name ,Region ,Phone ) "
                    + "values"
                    + " (?,?,?)");
            ps.setString(1, name);
            ps.setString(2, region);
            ps.setInt(3, phone);

            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        } finally {
            shutdownCon();
        }
        return error;
    }

    //Update Distributor data Row
    public boolean updateDistributorRow(String oldName, String name, String region, int phone) {

        try {
            ps = con.prepareStatement("UPDATE Distributor "
                    + "SET   Name=?, Region=?, Phone=? "
                    + " WHERE Name = ? ");

            ps.setString(1, name);
            ps.setString(2, region);
            ps.setInt(3, phone);
            ps.setString(4, oldName);

            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        } finally {
            shutdownCon();
        }
        return error;
    }

    //get Distributor data row to be displayed on TF
    public ArrayList<String> getDistributorRow(String distributorName) {
        ArrayList<String> distributorRow = new ArrayList<String>();
        try {
            ps = con.prepareStatement("SELECT Name ,Region ,Phone ,Quantity  "
                    + " FROM Distributor"
                    + " WHERE Name = ? ");
            ps.setString(1, distributorName);
            rs = ps.executeQuery();
            while (rs.next()) {
                distributorRow.add(rs.getString("Name"));
                distributorRow.add(rs.getString("Region"));
                distributorRow.add(rs.getInt("Phone") + "");
                distributorRow.add(rs.getInt("Quantity") + "");
            }
        } catch (Exception e) {
        } finally {
            shutdownCon();
        }
        return distributorRow;
    }

    //get Distributor Name to be displayed on Distributors ComboBox
    public ArrayList<String> getDistributorName() {
        ArrayList<String> names = new ArrayList<String>();
        try {
            ps = con.prepareStatement("select Name from Distributor");
            rs = ps.executeQuery();
            while (rs.next()) {
                names.add(rs.getString("Name"));
            }
        } catch (Exception e) {
        } finally {
            shutdownCon();
        }
        return names;

    }

    //get Distributor Name to be displayed on Distributors ComboBox
    public int getID(String distributorName) {
        int id = 0;
        try {
            ps = con.prepareStatement("select DistributorID from Distributor"
                    + " WHERE "
                    + " Name = ? ");
            ps.setString(1, distributorName);
            rs = ps.executeQuery();
            while (rs.next()) {
                id = rs.getInt("DistributorID");
            }
        } catch (Exception e) {
        } finally {
            shutdownCon();
        }
        return id;
    }

    //get Distributor Name to be displayed on Distributors ComboBox
    public String getRegion(int id) {
        String region = null;
        try {
            ps = con.prepareStatement("select Region from Distributor"
                    + " WHERE "
                    + " DistributorID = ? ");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                region = rs.getString("Region");
            }
        } catch (Exception e) {
        } finally {
            shutdownCon();
        }
        return region;
    }

    //get number of people with specific distributor
    public void getDistributorQuantity(int id) {
        try {
            ps = con.prepareStatement(" SELECT COUNT(*) FROM People "
                    + " WHERE "
                    + " DistributorID = ? ");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                quantity = rs.getInt(1);
                setDistributorQuantity(id, quantity);
            }
        } catch (Exception ex) {
        }
    }

    //Set number of people with specific distributor in it's quantity TF
    public void setDistributorQuantity(int id, int quantity) {

        try {
            ps = con.prepareStatement("UPDATE Distributor "
                    + "SET Quantity =?"
                    + " WHERE DistributorID = ? ");
            ps.setInt(1, quantity);
            ps.setInt(2, id);
            ps.executeUpdate();
        } catch (Exception ex) {
        }
    }

    //shutdown database connection
    private void shutdownCon() {
        try {
            DriverManager.getConnection("jdbc:derby:;shutdown=true");
        } catch (Exception ex) {
        }
    }
}
